package ztc.service;

import ztc.common.Constant;
import ztc.common.DBcon;
import ztc.common.SiginBean;
import ztc.common.StuBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import java.util.concurrent.ConcurrentHashMap;

/**
 * Created by ztc on 16-4-17.下午9:20
 */
public class DBTools {
    public List<SiginBean> getSiginList(Integer uid){
        String sql="select*from sigin where uid=? order by id desc;";
        Connection con= DBcon.getCon();
        List<SiginBean> list=new ArrayList<SiginBean>();
        try {
            PreparedStatement pr=con.prepareStatement(sql);
            pr.setInt(1,uid);
            ResultSet resultSet=pr.executeQuery();
            while(resultSet.next()){
                list.add(new SiginBean(resultSet.getInt("id"),
                        resultSet.getInt("uid"),
                        resultSet.getString("sname").split("\\"+Constant.separator)[1],
                        resultSet.getInt("size"),
                        resultSet.getString("foreground"),
                        resultSet.getString("background"),
                        resultSet.getString("time")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<StuBean> getStuListBySiginID(Integer siginid){
        String sql="select y.*,x.time sigintime,x.mac siginmac from sigin_stu x,students y where x.siginid=? and x.stuid=y.id order by sigintime asc";
        Connection con=DBcon.getCon();
        PreparedStatement pr;
        List<StuBean> list=new ArrayList<StuBean>();
        try {
            pr=con.prepareStatement(sql);
            pr.setInt(1,siginid);
            ResultSet resultSet=pr.executeQuery();
            while(resultSet.next()){
                list.add(new StuBean(resultSet.getString("id"),
                        resultSet.getString("name"),
                        resultSet.getString("sex"),
                        resultSet.getString("class"),
                        resultSet.getString("institute"),
                        resultSet.getString("siginmac"),
                        resultSet.getString("sigintime")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    public boolean insertSigin(SiginBean siginBean){
        if(!this.isSiginNameRepeat(siginBean)) {
            String sql = "insert into sigin(uid,sname,size,foreground,background,time) values(?,?,?,?,?,?);";
            Connection con = DBcon.getCon();
            PreparedStatement pr;
            try {
                pr = con.prepareStatement(sql);
                pr.setInt(1, siginBean.getUid());
                pr.setString(2, siginBean.getUid() + Constant.separator+ siginBean.getSname());
                pr.setInt(3,siginBean.getSize());
                pr.setString(4,siginBean.getForeground());
                pr.setString(5,siginBean.getBackground());
                pr.setString(6, siginBean.getTime());
                int result = pr.executeUpdate();
                pr.close();
                con.close();
                return result == 1;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
    public boolean saveSiginStu(ConcurrentHashMap<String,StuBean> map,String siginName){
        String sql="insert into sigin_stu values(?,?,?,?);";
        Connection con=DBcon.getCon();
        PreparedStatement pr;
        Integer siginID=this.getSiginIDbyName(siginName);
        try {
            pr=con.prepareStatement(sql);
            Set set=map.keySet();
            for(Object s:set){
                pr.setInt(1,siginID);
                pr.setString(2,map.get(s).getStuID());
                pr.setString(3,map.get(s).getMac());
                pr.setString(4,map.get(s).getTime());
                pr.executeUpdate();
            }
            pr.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return true;
    }
    public int getSiginSumBySiginID(Integer id){
        String sql="select count(*) from sigin_stu where siginid=?;";
        Connection con=DBcon.getCon();
        PreparedStatement pr;
        int sum=0;
        try {
            pr=con.prepareStatement(sql);
            pr.setInt(1,id);
            ResultSet resultSet=pr.executeQuery();
            if(resultSet.next())
                sum=resultSet.getInt(1);
            pr.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sum;
    }
    public boolean delSiginById(int siginid,int uid){
        String sql1="DELETE FROM sigin WHERE id=? and uid=?;";
        String sql2="DELETE FROM sigin_stu WHERE siginid=?;";
        Connection con=DBcon.getCon();
        try {
            PreparedStatement pr1=con.prepareStatement(sql1);
            pr1.setInt(1,siginid);
            pr1.setInt(2,uid);
            int result1=pr1.executeUpdate();
            pr1.close();
            if(result1==1) {
                PreparedStatement pr2=con.prepareStatement(sql2);
                pr2.setInt(1, siginid);
                pr2.executeUpdate();
                pr2.close();
            }
            con.close();
            return result1==1;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    public Integer getSiginIDbyName(String siginName){
        String sql="select id from sigin where sname=?;";
        Connection con=DBcon.getCon();
        PreparedStatement pr;
        Integer id=null;
        try {
            pr=con.prepareStatement(sql);
            pr.setString(1,siginName);
            ResultSet resultSet=pr.executeQuery();
            if(resultSet.next())
                id=resultSet.getInt("id");
            pr.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return id;
    }
    //判断sigin的Name是否重复
    public boolean isSiginNameRepeat(SiginBean siginBean){
        String sql="select*from sigin where sname=?";
        Connection con=DBcon.getCon();
        PreparedStatement pr;
        try {
            pr=con.prepareStatement(sql);
            pr.setString(1,siginBean.getUid()+Constant.separator+siginBean.getSname());
            ResultSet resultSet=pr.executeQuery();
            boolean result=resultSet.next();
            pr.close();
            con.close();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}
